
Imports System
Imports System.Collections
Imports System.Data.SqlClient
Imports System.Diagnostics
Imports System.Drawing
Imports System.IO
Imports _2bMatesSite.Common


Namespace _2bMatesSite.BO
	''' <summary>
	''' Represents a 2b mates group.
	''' </summary>
	Public Class Group

		Protected id As Integer
		Protected name As String
		Protected catID As Integer
		Protected uid As Integer
		Protected publicGroup As Boolean
		Protected autoApproveComments As Boolean
		Protected autoApproveBlogComments As Boolean
		Protected countryID As Integer
		Protected city As String
		Protected zipCode As String
		Protected about As String
		Protected shortAbout As String
		Protected activities As String
		Protected contacts As String
		Protected charity As String
		Protected songURL1 As String
		Protected songURLName1 As String
		Protected songURL2 As String
		Protected songURLName2 As String
		Protected songURL3 As String
		Protected songURLName3 As String
		Protected clipURL As String
		Protected clipURLName As String
		Protected imageURL As String
		Protected imageURLName As String
		Protected timesViewed As Integer
		Protected creationDate As DateTime
		Protected lastEditedDate As DateTime
		Protected defaultPictureID As Integer
		Protected featureGroup As Boolean

		'
		' Group constructors
		'

		Public Sub New(ByVal userID As Integer)
			Init(userID)
		End Sub

		' Loads group from a data reader
		Public Sub New(ByVal dr As SqlDataReader)
			LoadFromReader(dr)
		End Sub

		Public Overloads Overrides Function GetHashCode() As Integer
			Return id
		End Function

		'
		' Properties
		'

		Public ReadOnly Property ID() As Integer
			Get
				Return id
			End Get
		End Property

		Public Property Name() As String
			Get
				Return name
			End Get

			Set
				name = value
			End Set
		End Property

		Public Property CatID() As Integer
			Get
				Return catID
			End Get

			Set
				catID = value
			End Set
		End Property

		Public Property Uid() As Integer
			Get
				Return uid
			End Get

			Set
				uid = value
			End Set
		End Property

		Public Property PublicGroup() As Boolean
			Get
				Return publicGroup
			End Get

			Set
				publicGroup = value
			End Set
		End Property

		Public Property AutoApproveComments() As Boolean
			Get
				Return autoApproveComments
			End Get

			Set
				autoApproveComments = value
			End Set
		End Property

		Public Property AutoApproveBlogComments() As Boolean
			Get
				Return autoApproveBlogComments
			End Get

			Set
				autoApproveBlogComments = value
			End Set
		End Property

		Public Property CountryID() As Integer
			Get
				Return countryID
			End Get

			Set
				countryID = value
			End Set
		End Property

		Public Property City() As String
			Get
				Return city
			End Get

			Set
				city = value
			End Set
		End Property

		Public Property ZipCode() As String
			Get
				Return zipCode
			End Get

			Set
				zipCode = value
			End Set
		End Property

		Public Property About() As String
			Get
				Return about
			End Get

			Set
				about = value
			End Set
		End Property

		Public Property ShortAbout() As String
			Get
				Return shortAbout
			End Get

			Set
				shortAbout = value
			End Set
		End Property

		Public Property DefaultPictureID() As Integer
			Get
				Return defaultPictureID
			End Get

			Set
				defaultPictureID = value
			End Set
		End Property

		Public Property Activities() As String
			Get
				Return activities
			End Get

			Set
				activities = value
			End Set
		End Property

		Public Property Contacts() As String
			Get
				Return contacts
			End Get

			Set
				contacts = value
			End Set
		End Property

		Public Property Charity() As String
			Get
				Return charity
			End Get

			Set
				charity = value
			End Set
		End Property

		Public Property SongURL1() As String
			Get
				Return songURL1
			End Get

			Set
				songURL1 = value
			End Set
		End Property

		Public Property SongURLName1() As String
			Get
				Return songURLName1
			End Get

			Set
				songURLName1 = value
			End Set
		End Property

		Public Property SongURL2() As String
			Get
				Return songURL2
			End Get

			Set
				songURL2 = value
			End Set
		End Property

		Public Property SongURLName2() As String
			Get
				Return songURLName2
			End Get

			Set
				songURLName2 = value
			End Set
		End Property

		Public Property SongURL3() As String
			Get
				Return songURL3
			End Get

			Set
				songURL3 = value
			End Set
		End Property

		Public Property SongURLName3() As String
			Get
				Return songURLName3
			End Get

			Set
				songURLName3 = value
			End Set
		End Property

		Public Property ClipURL() As String
			Get
				Return clipURL
			End Get

			Set
				clipURL = value
			End Set
		End Property

		Public Property ClipURLName() As String
			Get
				Return clipURLName
			End Get

			Set
				clipURLName = value
			End Set
		End Property

		Public Property ImageURL() As String
			Get
				Return imageURL
			End Get

			Set
				imageURL = value
			End Set
		End Property

		Public Property ImageURLName() As String
			Get
				Return imageURLName
			End Get

			Set
				imageURLName = value
			End Set
		End Property

		Public Property TimesViewed() As Integer
			Get
				Return timesViewed
			End Get

			Set
				timesViewed = value
			End Set
		End Property

		Public ReadOnly Property LastEditedDate() As DateTime
			Get
				Return lastEditedDate
			End Get
		End Property

		Public ReadOnly Property CreationDate() As DateTime
			Get
				Return creationDate
			End Get
		End Property

		Public Property FeatureGroup() As Boolean
			Get
				Return featureGroup
			End Get

			Set
				featureGroup = value
			End Set
		End Property

		Public ReadOnly Property Members() As GroupsUsers
			Get
				Return New GroupsUsers(Me)
			End Get
		End Property

		Public ReadOnly Property PendingMembers() As GroupsPendingUsers
			Get
				Return New GroupsPendingUsers(Me)
			End Get
		End Property


		'
		' Public methods
		'


		' Save the modifications to the group made so far
		' If it's a new group inserts it into the database
		Public Sub Save()
			If id < 0 Then
				DoInsert()
			Else
				DoUpdate()
			End If
		End Sub

		' Request membership to the group
		' Creates group membership, sets it's status
		Public Function RequestMembership(ByVal newMemeber As User) As GroupMember.JoinStatus
			Return RequestMembership(newMemeber.ID)
		End Function

		' Request membership to the group
		' Creates group membership, sets it's status
		Public Function RequestMembership(ByVal newMemeberID As Integer) As GroupMember.JoinStatus
			Dim gm As GroupMember = GetUserMembership(newMemeberID)

			Dim publicGroupForThisUser As Boolean = publicGroup OrElse (newMemeberID = uid)
			gm.IncrementStatus(publicGroupForThisUser)
			gm.Save()

			Return gm.Status
		End Function

		' Accept a user's membership to the group
		' Accepts a group membership, on behalf of the callerUserID, and send a message to the accepted user, if needed
		' NOTE: Called ONLY by the moderator of the group (that's why the callerUserID is required)
		Public Function AcceptMembership(ByVal callerUserID As Integer, ByVal newMemeberID As Integer) As GroupMember.JoinStatus
			If callerUserID <> uid Then
				Throw New GroupMembershipException("Only moderators can manipulate group memberships")
			End If

			Dim gm As GroupMember = GetUserMembership(newMemeberID)
			gm.ModeratorMode = (callerUserID = uid)

			Dim publicGroupForThisUser As Boolean = publicGroup OrElse (newMemeberID = uid)
			gm.IncrementStatus(publicGroupForThisUser)
			gm.Save()

			Return gm.Status
		End Function

		' Reject a user's membership to the group
		' NOTE: Called ONLY by the moderator of the group (that's why the callerUserID is required)
		Public Sub RejectMembership(ByVal callerUserID As Integer, ByVal rejectedMemberID As Integer)
			If rejectedMemberID = uid Then
				Throw New GroupMembershipException("Can't reject moderator's membership")
			End If

			If callerUserID <> uid Then
				Throw New GroupMembershipException("Only moderators can manipulate group memberships")
			End If

			Dim gm As GroupMember = GetUserMembership(rejectedMemberID)
			gm.ModeratorMode = True

			gm.DecrementStatus()
			gm.Save()
		End Sub

		' Cancel a user's membership
		Public Sub CancelMembership(ByVal rejectedMemberID As Integer)
			If rejectedMemberID = uid Then
				Throw New GroupMembershipException("Can't cancel moderator's membership")
			End If

			Dim gm As GroupMember = GetUserMembership(rejectedMemberID)
			gm.DecrementStatus()
			gm.Save()
		End Sub

		' Returns and existing membership request if such exists
		' Or a new one if it doesn't, with the requester set to userID
		Public Function GetUserMembership(ByVal userID As Integer) As GroupMember
			Dim df As GroupMember = Nothing

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()
			Try
				Dim cmd As New SqlCommand("SELECT * FROM GroupMembers WHERE GroupID=@GroupID AND UID=@UID")
				cmd.Connection = conn
				cmd.Parameters.Add("@GroupID", id)
				cmd.Parameters.Add("@UID", userID)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				If dr.Read() Then
					df = New GroupMember(dr)
				Else
					df = New GroupMember(id, userID)
				End If

				dr.Close()
			Finally
				conn.Close()
			End Try

			Return df
		End Function



		Public Sub CountViews()
			DoUpdateCounter()

		End Sub

		Public Property DefaultPicture() As GroupPicture
			Get
				Return GetPicture(DefaultPictureID)
			End Get
			Set
				If value = Nothing Then
					defaultPictureID = -1
				Else
					If GetPicture(value.ID) <> Nothing Then
						defaultPictureID = value.ID
					End If
				End If
			End Set
		End Property

		Public Function GetPicture(ByVal picID As Integer) As GroupPicture
			Dim pic As GroupPicture = Nothing

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM GroupPictures WHERE GroupID=@GroupID AND ID=@ID")
				cmd.Connection = conn

				cmd.Parameters.Add("@GroupID", id)
				cmd.Parameters.Add("@ID", picID)

				Dim dr As SqlDataReader = cmd.ExecuteReader()

				If dr.Read() Then
					pic = New GroupPicture(dr)
				End If
				dr.Close()
			Finally
				conn.Close()
			End Try

			Return pic
		End Function

		Public Function GetPictures() As ArrayList
			Dim pics As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand(GetPicturesSQL())
				cmd.Connection = conn

				cmd.Parameters.Add("@GroupID", id)

				Dim dr As SqlDataReader = cmd.ExecuteReader()

				While dr.Read()
					pics.Add(New GroupPicture(dr))
				End While
				dr.Close()
			Finally
				conn.Close()
			End Try

			Return pics
		End Function

		Public Function GetPicturesTotalLength() As Integer
			Dim totalLength As Integer = 0

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand(GetPicturesTotalLengthSQL())
				cmd.Connection = conn

				cmd.Parameters.Add("@GroupID", id)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				If dr.Read() Then
					totalLength = DBManager.GetNZInt32(dr, "TotalLength")
				End If
				dr.Close()
			Finally
				conn.Close()
			End Try

			Return totalLength
		End Function

		Public Function AddPicture(ByVal picStream As Stream, ByVal picName As String, ByVal picDesc As String) As GroupPicture
			' Load the original picture
			Dim img As New Bitmap(picStream)

			' Calculate it's new size
			Dim newSize As Size = Pictures.FitSize(img.Size, New Size(Constants.PICTURE_MAX_WIDTH, Constants.PICTURE_MAX_HEIGHT), True)

			' Resize if needed
			If Not newSize.Equals(img.Size) Then
				img = Pictures.ResizeBitmap(img, newSize)
			End If

			' store the image and create an instance of the user's Picture object
			Dim pic As New GroupPicture(Me, img, picName, picDesc)
			pic.Save()

			Return pic
		End Function


		Public Sub DeletePicture(ByVal pic As GroupPicture)
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				' Delete the picture
				Dim cmd As New SqlCommand(GetDeletePictureSQL())
				cmd.Connection = conn

				cmd.Parameters.Add("@ID", pic.ID)

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)
			Finally
				conn.Close()
			End Try
		End Sub
		Public Sub AddComment(ByVal fromUserID As Integer, ByVal comment As String)
			Dim grpComment As New GroupComment(id, fromUserID, comment)
			If autoApproveComments Then
				grpComment.CommentStatus = GroupComment.Status.ucsAccepted
			End If

			AddComment(grpComment)
		End Sub

		Public Sub AddComment(ByVal comment As GroupComment)
			If comment.AboutGroupID <> id Then
				Throw New Exception("Can't add comments to a group about other roups.")
			End If

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim sql As String = "INSERT INTO GroupComments (Comment, PostedByUID, AboutGroupID, Status, Date)" + Environment.NewLine
				sql += "VALUES (@Comment, @PostedByUID, @AboutGroupID, @Status, @Date)" + Environment.NewLine

				Dim cmd As New SqlCommand(sql)
				cmd.Connection = conn
				cmd.Parameters.Add("@Comment", comment.Comment)
				cmd.Parameters.Add("@PostedByUID", comment.PostedByUserID)
				cmd.Parameters.Add("@AboutGroupID", comment.AboutGroupID)
				cmd.Parameters.Add("@Status", DirectCast(comment.CommentStatus, Integer))
				cmd.Parameters.Add("@Date", comment.[Date])

				cmd.ExecuteNonQuery()

				' Send the moderator a notification for groups with "Approve Member Comments" = Yes
				GroupNotifications.SendNewGroupCommentModeratorNotification(Users.GetUserByID(Groups.GetGroupByID(comment.AboutGroupID).Uid), comment.AboutGroupID, Users.GetUserByID(comment.PostedByUserID))
			Finally
				conn.Close()
			End Try
		End Sub

		Public Sub RemoveComment(ByVal commentID As Integer)
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim sql As String = "DELETE FROM GroupComments WHERE ID=@ID" + Environment.NewLine
				Dim cmd As New SqlCommand(sql)
				cmd.Connection = conn

				cmd.Parameters.Add("@ID", commentID)

				cmd.ExecuteNonQuery()
			Finally
				conn.Close()
			End Try
		End Sub

		Public Sub ApproveComment(ByVal commentID As Integer)
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim sql As String = "UPDATE GroupComments SET Status=" + DirectCast(GroupComment.Status.ucsAccepted, Integer) + " WHERE ID=@ID" + Environment.NewLine
				Dim cmd As New SqlCommand(sql)
				cmd.Connection = conn

				cmd.Parameters.Add("@ID", commentID)

				cmd.ExecuteNonQuery()
			Finally
				conn.Close()
			End Try
		End Sub

		Public ReadOnly Property NewComments() As ArrayList
			Get
				Return GetNewComments()
			End Get
		End Property

		Public ReadOnly Property Comments() As ArrayList
			Get
				Return GetApprovedComments()
			End Get
		End Property

		Public Sub DeleteSong(ByVal SongID As String, ByVal FileFolderPath As String)
			'deletes users song or clip
			'the procedure removes the data from the database nadee deleted the file form the server's harddisk
			Dim l_sFilePath As String = ""

			Select Case SongID
				Case "1"
					l_sFilePath = FileFolderPath + SongURL1
					File.Delete(l_sFilePath)

					SongURL1 = ""
					SongURLName1 = ""
					Save()
					Exit Select
				Case "2"

					l_sFilePath = FileFolderPath + SongURL2
					File.Delete(l_sFilePath)

					SongURL2 = ""
					SongURLName2 = ""
					Save()
					Exit Select
				Case "3"

					l_sFilePath = FileFolderPath + SongURL3
					File.Delete(l_sFilePath)

					SongURL3 = ""
					SongURLName3 = ""
					Save()
					Exit Select
				Case "4"

					l_sFilePath = FileFolderPath + ClipURL
					File.Delete(l_sFilePath)

					ClipURL = ""
					ClipURLName = ""
					Save()
					Exit Select
			End Select
		End Sub

		'
		' Private and protected methods
		'

		Protected Function GetNewComments() As ArrayList
			Dim cl As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM GroupComments WHERE AboutGroupID=@AboutGroupID AND Status=" + DirectCast(GroupComment.Status.ucsNew, Integer) + " ORDER BY DATE DESC")
				cmd.Connection = conn
				cmd.Parameters.Add("@AboutGroupID", id)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					cl.Add(New GroupComment(dr))
				End While
				dr.Close()
			Finally
				conn.Close()
			End Try

			Return cl
		End Function

		Protected Function GetApprovedComments() As ArrayList
			Dim cl As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM GroupComments WHERE AboutGroupID=@AboutGroupID AND Status=" + DirectCast(GroupComment.Status.ucsAccepted, Integer) + " ORDER BY DATE DESC")
				cmd.Connection = conn
				cmd.Parameters.Add("@AboutGroupID", id)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					cl.Add(New GroupComment(dr))
				End While
				dr.Close()
			Finally
				conn.Close()
			End Try

			Return cl
		End Function

		'
		' Private and protected methods
		'

		Protected Sub Init(ByVal userID As Integer)
			id = -1
			name = ""
			catID = -1
			uid = userID
			publicGroup = True
			autoApproveComments = False
			autoApproveBlogComments = False
			countryID = -1
			city = ""
			zipCode = ""
			about = ""
			shortAbout = ""
			activities = ""
			contacts = ""
			charity = ""
			songURL1 = ""
			songURLName1 = ""
			songURL2 = ""
			songURLName2 = ""
			songURL3 = ""
			songURLName3 = ""
			clipURL = ""
			clipURLName = ""
			imageURL = ""
			imageURLName = ""
			timesViewed = 0
			lastEditedDate = DateTime.Now
			creationDate = DateTime.Now
			defaultPictureID = -1
			featureGroup = False
		End Sub

		Protected Sub LoadFromReader(ByVal dr As SqlDataReader)
			id = dr.GetInt32(dr.GetOrdinal("ID"))

			name = dr.GetString(dr.GetOrdinal("Name"))

			catID = dr.GetInt32(dr.GetOrdinal("CatID"))

			uid = dr.GetInt32(dr.GetOrdinal("UID"))

			publicGroup = dr.GetBoolean(dr.GetOrdinal("PublicGroup"))

			autoApproveComments = dr.GetBoolean(dr.GetOrdinal("ApproveComments"))

			autoApproveBlogComments = dr.GetBoolean(dr.GetOrdinal("ApproveBlogComments"))

			countryID = dr.GetInt32(dr.GetOrdinal("CountryID"))

			city = DBManager.GetNZString(dr, "City")

			zipCode = DBManager.GetNZString(dr, "ZipCode")

			about = DBManager.GetNZString(dr, "About")

			shortAbout = DBManager.GetNZString(dr, "ShortAbout")

			activities = DBManager.GetNZString(dr, "Activities")

			contacts = DBManager.GetNZString(dr, "Contacts")

			charity = DBManager.GetNZString(dr, "Charity")

			songURL1 = DBManager.GetNZString(dr, "SongURL1")

			songURLName1 = DBManager.GetNZString(dr, "SongURLName1")

			songURL2 = DBManager.GetNZString(dr, "SongURL2")

			songURLName2 = DBManager.GetNZString(dr, "SongURLName2")

			songURL3 = DBManager.GetNZString(dr, "SongURL3")

			songURLName3 = DBManager.GetNZString(dr, "SongURLName3")

			clipURL = DBManager.GetNZString(dr, "ClipURL")

			clipURLName = DBManager.GetNZString(dr, "ClipURLName")

			imageURL = DBManager.GetNZString(dr, "ImageURL")

			imageURLName = DBManager.GetNZString(dr, "ImageURLName")

			timesViewed = dr.GetInt32(dr.GetOrdinal("TimesViewed"))

			lastEditedDate = dr.GetDateTime(dr.GetOrdinal("LastEditedDate"))

			creationDate = dr.GetDateTime(dr.GetOrdinal("Date"))

			defaultPictureID = DBManager.GetNZInt32(dr, "DefaultPictureID")

			featureGroup = dr.GetBoolean(dr.GetOrdinal("FeatureGroup"))

		End Sub

		Protected Sub DoInsert()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()
			Dim tran As SqlTransaction = conn.BeginTransaction()
			Dim newGrpID As Integer = -1

			Try
				' Insert the group
				Dim cmd As New SqlCommand(GetInsertSQL())
				cmd.Connection = conn
				cmd.Transaction = tran

				PopulateCommand(cmd)

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)

				' Reload the group
				DoRefresh(tran)

				cmd = New SqlCommand("SELECT * FROM Groups WHERE CatID=@CatID AND Name=@Name")
				cmd.Connection = conn
				cmd.Transaction = tran
				cmd.Parameters.Add("@CatID", catID)
				cmd.Parameters.Add("@Name", name)

				Dim dr As SqlDataReader = cmd.ExecuteReader()

				If dr.Read() Then
					Dim grp As New Group(dr)
					Debug.Assert(Not dr.Read())

					newGrpID = grp.ID
				End If
				dr.Close()

				If newGrpID > 0 Then
					' add the moderator as member
					cmd = New SqlCommand("INSERT INTO GroupMembers (GroupID, UID, Status, AcceptModeratorBulletin, AcceptMemberBulletin) VALUES (@GroupID, @UID, @AcceptModeratorBulletin, @AcceptMemberBulletin, @Status)")
					cmd.Connection = conn
					cmd.Transaction = tran
					cmd.Parameters.Add("@GroupID", newGrpID)
					cmd.Parameters.Add("@UID", uid)
					cmd.Parameters.Add("@AcceptModeratorBulletin", True)
					cmd.Parameters.Add("@AcceptMemberBulletin", True)
					cmd.Parameters.Add("@Status", DirectCast(GroupMember.JoinStatus.jsAccepted, Integer))

					rows = cmd.ExecuteNonQuery()
					Debug.Assert(rows = 1)
				End If
				tran.Commit()
			Catch e As Exception
				tran.Rollback()
				Throw e
			Finally
				conn.Close()
			End Try
		End Sub

		Protected Sub DoUpdate()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()
			Dim tran As SqlTransaction = conn.BeginTransaction()

			Try
				Dim cmd As New SqlCommand(GetUpdateSQL())
				cmd.Connection = conn
				cmd.Transaction = tran

				PopulateCommand(cmd)

				cmd.Parameters.Add("@ID", id)

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)

				tran.Commit()
			Catch e As Exception
				tran.Rollback()
				Throw e
			Finally
				conn.Close()
			End Try

			If publicGroup Then
				AcceptAllUsers()
			End If

			If autoApproveBlogComments Then
				ApproveAllBlogComments()
			End If

			If autoApproveComments Then
				ApproveAllComments()
				ApproveAllPictureComments()
			End If
		End Sub

		Protected Sub AcceptAllUsers()
			For Each gm As GroupMember In PendingMembers
				AcceptMembership(uid, gm.UserID)
			Next
		End Sub

		Protected Sub ApproveAllComments()
			For Each gc As GroupComment In GetNewComments()
				ApproveComment(gc.ID)
			Next
		End Sub

		Protected Sub ApproveAllPictureComments()
			For Each pic As GroupPicture In GetPictures()
				For Each gpc As PictureComment In pic.NewComments
					pic.ApproveComment(gpc.ID)
				Next
			Next
		End Sub

		Protected Sub ApproveAllBlogComments()
			For Each be As GroupBlog In Messages.GetNotApprovedGroupBlogEntries(id)
				be.Approve()
				be.Save()
			Next
		End Sub

		Protected Sub DoUpdateCounter()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()
			Dim tran As SqlTransaction = conn.BeginTransaction()

			Try
				Dim cmd As New SqlCommand(GetUpdateCounterSQL())
				cmd.Connection = conn
				cmd.Transaction = tran

				cmd.Parameters.Add("@TimesViewed", timesViewed + 1)
				cmd.Parameters.Add("@ID", id)

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)

				' Reload the group
				DoRefresh(tran)

				tran.Commit()
			Catch e As Exception
				tran.Rollback()
				Throw e
			Finally
				conn.Close()
			End Try
		End Sub

		Protected Sub DoRefresh(ByVal sqlTrans As SqlTransaction)
			Dim conn As SqlConnection = Nothing
			Dim ownConnection As Boolean = False

			If sqlTrans = Nothing Then
				conn = DBManager.GetUnmanagedConnection()
				ownConnection = True
			Else
				conn = sqlTrans.Connection
			End If

			Try
				Dim cmd As New SqlCommand("SELECT * FROM Groups WHERE Name=@Name AND CatID = @CatID")
				cmd.Connection = conn
				If Not ownConnection Then
					cmd.Transaction = sqlTrans
				End If

				cmd.Parameters.Add("@Name", name)
				cmd.Parameters.Add("@CatID", catID)

				Dim dr As SqlDataReader = cmd.ExecuteReader()

				Dim found As Boolean = dr.Read()
				Debug.Assert(found)
				LoadFromReader(dr)

				dr.Close()
			Finally
				If ownConnection Then
					conn.Close()
				End If
			End Try
		End Sub

		Public Sub DoDelete()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				' Send mail to all members that the group is going to be deleted
				Dim grpMembers As GroupsUsers = Me.Members
				Dim usrModerator As User = Users.GetUserByID(Me.Uid)

				' send notification to moderator
				If usrModerator.EnableGroupEmailNotify Then
					GroupNotifications.SendGroupCanceledModeratorNotification(usrModerator, Me.ID)
				End If

				' send notification to members
				For Each gm As GroupMember In grpMembers
					If Not gm.User.EnableGroupEmailNotify Then
						Continue For
					End If

					If gm.UserID <> Me.Uid Then
						GroupNotifications.SendGroupCanceledMemberNotification(gm, Me.ID)
					End If
				Next

				' Delete all comments, bulletins, and blogs
				DeleteAllComments()
				Messages.DeleteGroupBlogs(id)
				Messages.RemoveGroupFromGroupBulletins(id)

				' Delete the group
				Dim cmd As New SqlCommand(GetDeleteSQL())
				cmd.Connection = conn

				cmd.Parameters.Add("@ID", id)
				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)

				Constants.CATEGORIES = New BO.Categories()
			Finally
				conn.Close()
			End Try
		End Sub

		Private Sub DeleteAllComments()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("DELETE FROM GroupComments WHERE AboutGroupID=@AboutGroupID")
				cmd.Connection = conn

				cmd.Parameters.Add("@AboutGroupID", id)

				cmd.ExecuteNonQuery()
			Finally
				conn.Close()
			End Try
		End Sub

		Private Shared Function GetDeleteSQL() As String
			Dim sql As String = "DELETE FROM Groups" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetInsertSQL() As String
			Dim sql As String = "INSERT INTO Groups" + Environment.NewLine
			sql += "( Name, CatID, UID, PublicGroup, ApproveComments, ApproveBlogComments, CountryID, " + "City, ZipCode, About, ShortAbout, Activities, Contacts, Charity, " + "SongURL1, SongURLName1, SongURL2, SongURLName2, SongURL3, SongURLName3, " + "ClipURL, ClipURLName, ImageURL, ImageURLName, TimesViewed, LastEditedDate, DefaultPictureID )" + Environment.NewLine
			sql += "VALUES" + Environment.NewLine
			sql += "( @Name, @CatID, @UID, @PublicGroup, @ApproveComments, @ApproveBlogComments, @CountryID, " + "@City, @ZipCode, @About, @ShortAbout, @Activities, @Contacts, @Charity, " + "@SongURL1, @SongURLName1, @SongURL2, @SongURLName2, @SongURL3, @SongURLName3, " + "@ClipURL, @ClipURLName, @ImageURL, @ImageURLName, @TimesViewed, @LastEditedDate, @DefaultPictureID )" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetUpdateSQL() As String
			Dim sql As String = "UPDATE Groups SET" + Environment.NewLine
			sql += "" & Chr(9) & "Name=@Name," + Environment.NewLine
			sql += "" & Chr(9) & "CatID=@CatID," + Environment.NewLine
			sql += "" & Chr(9) & "UID=@UID," + Environment.NewLine
			sql += "" & Chr(9) & "PublicGroup=@PublicGroup," + Environment.NewLine
			sql += "" & Chr(9) & "ApproveComments=@ApproveComments," + Environment.NewLine
			sql += "" & Chr(9) & "ApproveBlogComments=@ApproveBlogComments," + Environment.NewLine
			sql += "" & Chr(9) & "CountryID=@CountryID," + Environment.NewLine
			sql += "" & Chr(9) & "City=@City," + Environment.NewLine
			sql += "" & Chr(9) & "ZipCode=@ZipCode," + Environment.NewLine
			sql += "" & Chr(9) & "About=@About," + Environment.NewLine
			sql += "" & Chr(9) & "ShortAbout=@ShortAbout," + Environment.NewLine
			sql += "" & Chr(9) & "Activities=@Activities," + Environment.NewLine
			sql += "" & Chr(9) & "Contacts=@Contacts," + Environment.NewLine
			sql += "" & Chr(9) & "Charity=@Charity," + Environment.NewLine
			sql += "" & Chr(9) & "SongURL1=@SongURL1," + Environment.NewLine
			sql += "" & Chr(9) & "SongURLName1=@SongURLName1," + Environment.NewLine
			sql += "" & Chr(9) & "SongURL2=@SongURL2," + Environment.NewLine
			sql += "" & Chr(9) & "SongURLName2=@SongURLName2," + Environment.NewLine
			sql += "" & Chr(9) & "SongURL3=@SongURL3," + Environment.NewLine
			sql += "" & Chr(9) & "SongURLName3=@SongURLName3," + Environment.NewLine
			sql += "" & Chr(9) & "ClipURL=@ClipURL," + Environment.NewLine
			sql += "" & Chr(9) & "ClipURLName=@ClipURLName," + Environment.NewLine
			sql += "" & Chr(9) & "ImageURL=@ImageURL," + Environment.NewLine
			sql += "" & Chr(9) & "ImageURLName=@ImageURLName," + Environment.NewLine
			sql += "" & Chr(9) & "TimesViewed=@TimesViewed," + Environment.NewLine
			sql += "" & Chr(9) & "LastEditedDate=@LastEditedDate," + Environment.NewLine
			sql += "" & Chr(9) & "DefaultPictureID=@DefaultPictureID" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetUpdateCounterSQL() As String
			Dim sql As String = "UPDATE Groups SET" + Environment.NewLine
			sql += "" & Chr(9) & "TimesViewed=@TimesViewed" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetPicturesSQL() As String
			Dim sql As String = "SELECT pics.*," + Environment.NewLine
			sql += "" & Chr(9) & "CASE" + Environment.NewLine
			sql += "" & Chr(9) & "" & Chr(9) & "WHEN (pics.ID = grps.DefaultPictureID) THEN 1" + Environment.NewLine
			sql += "" & Chr(9) & "" & Chr(9) & "ELSE 0" + Environment.NewLine
			sql += "" & Chr(9) & "END AS IS_DEFAULT" + Environment.NewLine
			sql += "FROM" + Environment.NewLine
			sql += "" & Chr(9) & "GroupPictures pics," + Environment.NewLine
			sql += "" & Chr(9) & "Groups grps" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "pics.GroupID = grps.ID" + Environment.NewLine
			sql += "AND" & Chr(9) & "pics.GroupID=@GroupID" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetPicturesTotalLengthSQL() As String
			Dim sql As String = "SELECT SUM(DATALENGTH(Picture)) as TotalLength" + Environment.NewLine
			sql += "FROM" + Environment.NewLine
			sql += "" & Chr(9) & "GroupPictures pics" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "pics.GroupID = @GroupID" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetDeletePictureSQL() As String
			Dim sql As String = "DELETE FROM GroupPictures" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
			Return sql
		End Function


		' Populates the command with the groups's properties
		' NOTE: skips the ID property, since it's need only on insert
		Private Sub PopulateCommand(ByVal cmd As SqlCommand)
			cmd.Parameters.Add("@Name", name)
			cmd.Parameters.Add("@CatID", catID)
			cmd.Parameters.Add("@UID", uid)
			cmd.Parameters.Add("@PublicGroup", publicGroup)
			cmd.Parameters.Add("@ApproveComments", autoApproveComments)
			cmd.Parameters.Add("@ApproveBlogComments", autoApproveBlogComments)
			cmd.Parameters.Add("@CountryID", countryID)
			DBManager.SetNZValue(cmd, "@City", city)
			DBManager.SetNZValue(cmd, "@ZipCode", zipCode)
			DBManager.SetNZValue(cmd, "@About", about)
			DBManager.SetNZValue(cmd, "@ShortAbout", shortAbout)
			DBManager.SetNZValue(cmd, "@Activities", activities)
			DBManager.SetNZValue(cmd, "@Contacts", contacts)
			DBManager.SetNZValue(cmd, "@Charity", charity)
			DBManager.SetNZValue(cmd, "@SongURL1", songURL1)
			DBManager.SetNZValue(cmd, "@SongURLName1", songURLName1)
			DBManager.SetNZValue(cmd, "@SongURL2", songURL2)
			DBManager.SetNZValue(cmd, "@SongURLName2", songURLName2)
			DBManager.SetNZValue(cmd, "@SongURL3", songURL3)
			DBManager.SetNZValue(cmd, "@SongURLName3", songURLName3)
			DBManager.SetNZValue(cmd, "@ClipURL", clipURL)
			DBManager.SetNZValue(cmd, "@ClipURLName", clipURLName)
			DBManager.SetNZValue(cmd, "@ImageURL", imageURL)
			DBManager.SetNZValue(cmd, "@ImageURLName", imageURLName)
			cmd.Parameters.Add("@TimesViewed", timesViewed)
			cmd.Parameters.Add("@LastEditedDate", DateTime.Now)
			DBManager.SetNZValue(cmd, "@DefaultPictureID", defaultPictureID)
		End Sub
	End Class
End Namespace






